# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import pymysql
from scrapyDemo.logg import logger

class ScrapydemoPipeline(object):
    '''爬取结果打印'''
    def process_item(self, item, spider):
        return item

class ScrapydemoMySqlPipeline:
    '''本地数据入库操作管道'''
    conn = None
    cursor = None
    def open_spider(self,spider):
        # 连接数据库
        self.connect = pymysql.connect(
            host='你的数据库ip', port=3306, user='数据库账号', password='数据库密码', database='数据库', use_unicode=True)

        # 通过cursor执行增删查改
        self.cursor = self.connect.cursor()

    def process_item(self, item, spider):
        # 插入数据
        self.cursor.execute('insert into dangdang(name, author, introduction, price ,press) value ( "%s", "%s", "%s", "%s", "%s")'
            %(item['name'], item['author'], item['introduction'][-1], item['price'], item['press']))

        # 提交sql语句
        self.connect.commit()
        return item

class ScrapyblibliMySqlPipeline:
    '''搜索管道入库操作管道'''
    conn = None
    cursor = None
    def open_spider(self,spider):
        # 连接数据库
        self.connect = pymysql.connect(
            host='你的数据库ip', port=3306, user='数据库账号', password='数据库密码', database='数据库', use_unicode=True)

        # 通过cursor执行增删查改
        self.cursor = self.connect.cursor()

    def process_item(self, item, spider):
        # 插入数据
        if 'comment' in item :
            self.cursor.execute('insert into comments(pl) value ( "%s")' % (item['comment']))
            self.connect.commit()
        else:
            self.cursor.execute(
                'insert into blibli_search(name, href) value ( "%s", "%s" )' % (item['name'], item['href']))
            self.connect.commit()
        return item

class blibliHotList:
    '''热文入库操作管道'''
    conn = None
    cursor = None
    def open_spider(self,spider):
        # 连接数据库
        self.connect = pymysql.connect(
            host='你的数据库ip', port=3306, user='数据库账号', password='数据库密码', database='数据库', use_unicode=True)

        # 通过cursor执行增删查改
        self.cursor = self.connect.cursor()

    def process_item(self, item, spider):
        if spider.name == 'blibliHotList':
            try:
                # 插入数据
                self.cursor.execute('insert into bliblihotlist(hotTitle,hotID, hotName, hotUrl ,hotVideo,hotComment,hotType,hotTime) value ( "%s","%s", "%s", "%s", %s, %s ,"%s" ,"%s")'
                    %(item['hotTitle'],item['hotID'], item['hotName'], item['hotUrl'], item['hotVideo'], item['hotComment'],item['hotType'], item['hotTime']))

                # 提交sql语句
                self.connect.commit()
            except Exception as e:
                logger.error(f"{item}入库失败：{e}")
        elif spider.name == 'blibli_user':
            try:
                account_sql = f'SELECT * FROM `blibli_user` WHERE `Mid` = "{item["userMid"]}"'
                self.cursor.execute(account_sql)
                datas = self.cursor.fetchall()
                if datas == ():
                        # 插入数据
                    self.cursor.execute(
                        'insert into blibli_user(Mid,Name,Sex,Image,Fans,Follower,Url) value ( "%s","%s", "%s", "%s", %s, %s ,"%s")'
                        % (item['userMid'], item['userName'], item['userSex'], item['userImage'], item['userFans'],
                           item['userFollower'], item['userUrl']))

                    # 提交sql语句
                    self.connect.commit()
                else:
                    account_sql = 'update `blibli_user` set  Fans = %s, Follower = %s, Image  = %s where Mid = "%s"'
                    aa = (item['userFans'], item['userFollower'], item['userImage'], item['userMid'])
                    self.cursor.execute(account_sql, aa)
                    self.connect.commit()
            except Exception as e:
                logger.error(f"{item}入库失败：{e}")

